﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class ContractDA : IContractDA
    {
        private string ALL_BILLS_LOWEST_CHARGE = "@ALL_BILLS_LOWEST_CHARGE";
        private string ALL_BILLS_LOWEST_GENERAL_LEDGER = "@ALL_BILLS_LOWEST_GENERAL_LEDGER";
        private string ALL_BILLS_LOWEST_TAX_GROUP = "@ALL_BILLS_LOWEST_TAX_GROUP";
        private string CONTRACT_ID = "@CONTRACT_ID";
        private string CONTRACT_TYPE = "@CONTRACT_TYPE";
        private string CREATED_BY = "@CREATED_BY";
        private string DEPARTMENT_ID = "@DEPARTMENT_ID";
        private string DEPOSIT = "@DEPOSIT";
        private string DESCR = "@DESCR";
        private string END_TIME = "@END_TIME";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string IS_AUDIT = "@IS_AUDIT";
        private string LOWEST_INBOUND_BILL_CHARGE = "@LOWEST_INBOUND_BILL_CHARGE";
        private string LOWEST_INBOUND_BILL_GENERAL_LEDGER = "@LOWEST_INBOUND_BILL_GENERAL_LEDGER";
        private string LOWEST_INBOUND_BILL_TAX_GROUP = "@LOWEST_INBOUND_BILL_TAX_GROUP";
        private string LOWEST_INBOUND_CHARGE = "@LOWEST_INBOUND_CHARGE";
        private string LOWEST_INBOUND_GENERAL_LEDGER = "@LOWEST_INBOUND_GENERAL_LEDGER";
        private string LOWEST_INBOUND_TAX_GROUP = "@LOWEST_INBOUND_TAX_GROUP";
        private string LOWEST_INITIAL_STORAGE_BILL_CHARGE = "@LOWEST_INITIAL_STORAGE_BILL_CHARGE";
        private string LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER = "@LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER";
        private string LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP = "@LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP";
        private string LOWEST_INITIAL_STORAGE_CHARGE = "@LOWEST_INITIAL_STORAGE_CHARGE";
        private string LOWEST_INITIAL_STORAGE_GENERAL_LEDGER = "@LOWEST_INITIAL_STORAGE_GENERAL_LEDGER";
        private string LOWEST_INITIAL_STORAGE_TAX_GROUP = "@LOWEST_INITIAL_STORAGE_TAX_GROUP";
        private string LOWEST_OUTBOUND_CHARGE = "@LOWEST_OUTBOUND_CHARGE";
        private string LOWEST_OUTBOUND_GENERAL_LEDGER = "@LOWEST_OUTBOUND_GENERAL_LEDGER";
        private string LOWEST_OUTBOUND_TAX_GROUP = "@LOWEST_OUTBOUND_TAX_GROUP";
        private string LOWEST_RECURRING_STORAGE_BILL_CHARGE = "@LOWEST_RECURRING_STORAGE_BILL_CHARGE";
        private string LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER = "@LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER";
        private string LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP = "@LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP";
        private string MANAGE_TYPE = "@MANAGE_TYPE";
        private string OVERDUE_RENT = "@OVERDUE_RENT";
        private string OWNER_ID = "@OWNER_ID";
        private string PAYMENT = "@PAYMENT";
        private string REMARK = "@REMARK";
        private string SETTLE_ACCOUNT = "@SETTLE_ACCOUNT";
        private string SQL_CHECK_CONTRACT_ID_UNIQUE = " SELECT COUNT(1) FROM @_@CONTRACT WHERE CONTRACT_ID = @CONTRACT_ID  ";
        private string SQL_DELETE_CONTRACT = " DELETE FROM @_@CONTRACT WHERE CONTRACT_ID = @CONTRACT_ID ";
        private string SQL_INSERT_CONTRACT = " INSERT INTO @_@CONTRACT ( WH_ID, CONTRACT_ID, OWNER_ID, DESCR, CONTRACT_TYPE, PAYMENT, SETTLE_ACCOUNT, START_TIME, END_TIME, DEPOSIT, WH_LOAD, MANAGE_TYPE, OVERDUE_RENT, LOWEST_INBOUND_CHARGE, LOWEST_INBOUND_TAX_GROUP, LOWEST_INBOUND_GENERAL_LEDGER, LOWEST_OUTBOUND_CHARGE, LOWEST_OUTBOUND_TAX_GROUP, LOWEST_OUTBOUND_GENERAL_LEDGER, LOWEST_INITIAL_STORAGE_CHARGE, LOWEST_INITIAL_STORAGE_TAX_GROUP, LOWEST_INITIAL_STORAGE_GENERAL_LEDGER, LOWEST_INBOUND_BILL_CHARGE, LOWEST_INBOUND_BILL_TAX_GROUP, LOWEST_INBOUND_BILL_GENERAL_LEDGER, LOWEST_INITIAL_STORAGE_BILL_CHARGE, LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP, LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER, LOWEST_RECURRING_STORAGE_BILL_CHARGE, LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP, LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER, ALL_BILLS_LOWEST_CHARGE, ALL_BILLS_LOWEST_TAX_GROUP, ALL_BILLS_LOWEST_GENERAL_LEDGER, USER1, USER2, USER3, USER4, USER5, USER6, USER7, USER8, USER9, USER10, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,DEPARTMENT_ID,IS_AUDIT,REMARK ) VALUES ( @WH_ID, @CONTRACT_ID, @OWNER_ID, @DESCR, @CONTRACT_TYPE, @PAYMENT, @SETTLE_ACCOUNT, @START_TIME, @END_TIME, @DEPOSIT, @WH_LOAD, @MANAGE_TYPE, @OVERDUE_RENT, @LOWEST_INBOUND_CHARGE, @LOWEST_INBOUND_TAX_GROUP, @LOWEST_INBOUND_GENERAL_LEDGER, @LOWEST_OUTBOUND_CHARGE, @LOWEST_OUTBOUND_TAX_GROUP, @LOWEST_OUTBOUND_GENERAL_LEDGER, @LOWEST_INITIAL_STORAGE_CHARGE, @LOWEST_INITIAL_STORAGE_TAX_GROUP, @LOWEST_INITIAL_STORAGE_GENERAL_LEDGER, @LOWEST_INBOUND_BILL_CHARGE, @LOWEST_INBOUND_BILL_TAX_GROUP, @LOWEST_INBOUND_BILL_GENERAL_LEDGER, @LOWEST_INITIAL_STORAGE_BILL_CHARGE, @LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP, @LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER, @LOWEST_RECURRING_STORAGE_BILL_CHARGE, @LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP, @LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER, @ALL_BILLS_LOWEST_CHARGE, @ALL_BILLS_LOWEST_TAX_GROUP, @ALL_BILLS_LOWEST_GENERAL_LEDGER, @USER1, @USER2, @USER3, @USER4, @USER5, @USER6, @USER7, @USER8, @USER9, @USER10, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@DEPARTMENT_ID,@IS_AUDIT,@REMARK  )  ";
        private string SQL_SELECT_ALL_CONTRACT = " SELECT  ROWID, WH_ID, CONTRACT_ID, OWNER_ID, DESCR, CONTRACT_TYPE, PAYMENT, SETTLE_ACCOUNT, START_TIME, END_TIME, DEPOSIT, WH_LOAD, MANAGE_TYPE, OVERDUE_RENT, LOWEST_INBOUND_CHARGE, LOWEST_INBOUND_TAX_GROUP, LOWEST_INBOUND_GENERAL_LEDGER, LOWEST_OUTBOUND_CHARGE, LOWEST_OUTBOUND_TAX_GROUP, LOWEST_OUTBOUND_GENERAL_LEDGER, LOWEST_INITIAL_STORAGE_CHARGE, LOWEST_INITIAL_STORAGE_TAX_GROUP, LOWEST_INITIAL_STORAGE_GENERAL_LEDGER, LOWEST_INBOUND_BILL_CHARGE, LOWEST_INBOUND_BILL_TAX_GROUP, LOWEST_INBOUND_BILL_GENERAL_LEDGER, LOWEST_INITIAL_STORAGE_BILL_CHARGE, LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP, LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER, LOWEST_RECURRING_STORAGE_BILL_CHARGE, LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP, LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER, ALL_BILLS_LOWEST_CHARGE, ALL_BILLS_LOWEST_TAX_GROUP, ALL_BILLS_LOWEST_GENERAL_LEDGER, USER1, USER2, USER3, USER4, USER5, USER6, USER7, USER8, USER9, USER10, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,DEPARTMENT_ID,IS_AUDIT,REMARK  FROM  @_@CONTRACT ";
        private string SQL_SELECT_ALL_CONTRACT_LANGUAGE = "SELECT CT.ROWID, CT.WH_ID, CT.CONTRACT_ID, CT.OWNER_ID, CT.DESCR, T.[TEXT] AS CONTRACT_TYPE, CT.PAYMENT, T1.[TEXT] AS SETTLE_ACCOUNT, CT.START_TIME, CT.END_TIME, CT.DEPOSIT, CT.WH_LOAD, CT.MANAGE_TYPE, CT.OVERDUE_RENT, CT.LOWEST_INBOUND_CHARGE, CT.LOWEST_INBOUND_TAX_GROUP, CT.LOWEST_INBOUND_GENERAL_LEDGER, CT.LOWEST_OUTBOUND_CHARGE, CT.LOWEST_OUTBOUND_TAX_GROUP, CT.LOWEST_OUTBOUND_GENERAL_LEDGER, CT.LOWEST_INITIAL_STORAGE_CHARGE, CT.LOWEST_INITIAL_STORAGE_TAX_GROUP, CT.LOWEST_INITIAL_STORAGE_GENERAL_LEDGER, CT.LOWEST_INBOUND_BILL_CHARGE, CT.LOWEST_INBOUND_BILL_TAX_GROUP, CT.LOWEST_INBOUND_BILL_GENERAL_LEDGER, CT.LOWEST_INITIAL_STORAGE_BILL_CHARGE, CT.LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP, CT.LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER, CT.LOWEST_RECURRING_STORAGE_BILL_CHARGE, CT.LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP,CT.LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER, CT.ALL_BILLS_LOWEST_CHARGE, CT.ALL_BILLS_LOWEST_TAX_GROUP, CT.ALL_BILLS_LOWEST_GENERAL_LEDGER, CT.USER1, CT.USER2, CT.USER3, CT.USER4, CT.USER5, CT.USER6, CT.USER7, CT.USER8, CT.USER9, CT.USER10, CT.IS_ACTIVE, CT.TS, CT.CREATED_BY, CT.CREATED_DATE, CT.UPDATED_BY, CT.UPDATED_DATE,CT.DEPARTMENT_ID,CT.IS_AUDIT,CT.REMARK  FROM @_@CONTRACT CT LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = CT.CONTRACT_TYPE AND SCD.SYS_CODE_TYPE = 'CONTRACTTYPE' LEFT JOIN SYSADM.TRANSLATION T ON 'CONTRACTTYPE.'+CT.CONTRACT_TYPE = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY =@LANGUAGE LEFT JOIN @_@SYS_CODE_DETAIL SCD1 ON SCD1.SYS_CODE = CT.SETTLE_ACCOUNT  AND SCD1.SYS_CODE_TYPE = 'SETTLEMENTMODE' LEFT JOIN SYSADM.TRANSLATION T1 ON 'SETTLEMENTMODE.'+CT.SETTLE_ACCOUNT = T1.ITEM_ID  AND  T1.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY =@LANGUAGE ";
        private string SQL_UPDATE_CONTRACT = " UPDATE @_@CONTRACT SET WH_ID = @WH_ID, OWNER_ID = @OWNER_ID, DESCR = @DESCR, CONTRACT_TYPE = @CONTRACT_TYPE, PAYMENT = @PAYMENT, SETTLE_ACCOUNT = @SETTLE_ACCOUNT, START_TIME = @START_TIME, END_TIME = @END_TIME, DEPOSIT = @DEPOSIT, WH_LOAD = @WH_LOAD, MANAGE_TYPE = @MANAGE_TYPE, OVERDUE_RENT = @OVERDUE_RENT, LOWEST_INBOUND_CHARGE = @LOWEST_INBOUND_CHARGE, LOWEST_INBOUND_TAX_GROUP = @LOWEST_INBOUND_TAX_GROUP, LOWEST_INBOUND_GENERAL_LEDGER = @LOWEST_INBOUND_GENERAL_LEDGER, LOWEST_OUTBOUND_CHARGE = @LOWEST_OUTBOUND_CHARGE, LOWEST_OUTBOUND_TAX_GROUP = @LOWEST_OUTBOUND_TAX_GROUP, LOWEST_OUTBOUND_GENERAL_LEDGER = @LOWEST_OUTBOUND_GENERAL_LEDGER, LOWEST_INITIAL_STORAGE_CHARGE = @LOWEST_INITIAL_STORAGE_CHARGE, LOWEST_INITIAL_STORAGE_TAX_GROUP = @LOWEST_INITIAL_STORAGE_TAX_GROUP, LOWEST_INITIAL_STORAGE_GENERAL_LEDGER = @LOWEST_INITIAL_STORAGE_GENERAL_LEDGER, LOWEST_INBOUND_BILL_CHARGE = @LOWEST_INBOUND_BILL_CHARGE, LOWEST_INBOUND_BILL_TAX_GROUP = @LOWEST_INBOUND_BILL_TAX_GROUP, LOWEST_INBOUND_BILL_GENERAL_LEDGER = @LOWEST_INBOUND_BILL_GENERAL_LEDGER, LOWEST_INITIAL_STORAGE_BILL_CHARGE = @LOWEST_INITIAL_STORAGE_BILL_CHARGE, LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP = @LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP, LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER = @LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER, LOWEST_RECURRING_STORAGE_BILL_CHARGE = @LOWEST_RECURRING_STORAGE_BILL_CHARGE, LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP = @LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP, LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER = @LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER, ALL_BILLS_LOWEST_CHARGE = @ALL_BILLS_LOWEST_CHARGE, ALL_BILLS_LOWEST_TAX_GROUP = @ALL_BILLS_LOWEST_TAX_GROUP, ALL_BILLS_LOWEST_GENERAL_LEDGER = @ALL_BILLS_LOWEST_GENERAL_LEDGER, USER1 = @USER1, USER2 = @USER2, USER3 = @USER3, USER4 = @USER4, USER5 = @USER5, USER6 = @USER6, USER7 = @USER7, USER8 = @USER8, USER9 = @USER9, USER10 = @USER10, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() ,DEPARTMENT_ID=@DEPARTMENT_ID, IS_AUDIT=@IS_AUDIT, REMARK=@REMARK  WHERE CONTRACT_ID = @CONTRACT_ID  ";
        private string SQL_UPDATE_CONTRACT_AUDIT = " UPDATE @_@CONTRACT SET IS_AUDIT=@IS_AUDIT,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE()  WHERE CONTRACT_ID = @CONTRACT_ID ";
        private string START_TIME = "@START_TIME";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string USER1 = "@USER1";
        private string USER10 = "@USER10";
        private string USER2 = "@USER2";
        private string USER3 = "@USER3";
        private string USER4 = "@USER4";
        private string USER5 = "@USER5";
        private string USER6 = "@USER6";
        private string USER7 = "@USER7";
        private string USER8 = "@USER8";
        private string USER9 = "@USER9";
        private string WH_ID = "@WH_ID";
        private string WH_LOAD = "@WH_LOAD";

        public int AuditContractByContractID(DataBase dataBase, DbTransaction tran, string contractID, string audit, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTRACT_ID", contractID), new SqlParameter("@IS_AUDIT", audit), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CONTRACT_AUDIT.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public bool CheckContractIDUnique(string contractID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTRACT_ID", contractID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_CONTRACT_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckContractIDUnique(DataBase dataBase, DbTransaction tran, string contractID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTRACT_ID", contractID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_CONTRACT_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteContract(List<string> contractID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < contractID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTRACT_ID", contractID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CONTRACT.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteContract(DataBase dataBase, DbTransaction tran, List<string> contractID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < contractID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTRACT_ID", contractID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CONTRACT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllContract(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_CONTRACT.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllContract(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_CONTRACT.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public ContractInfo GetContractByID(string contractID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_CONTRACT.Replace("@_@", whLoginID) + " WHERE CONTRACT_ID = @CONTRACT_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTRACT_ID", contractID) };
            ContractInfo contractInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    contractInfo = new ContractInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CONTRACT_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["DESCR"].ToString(), reader["CONTRACT_TYPE"].ToString(), (reader["PAYMENT"].ToString() != "") ? decimal.Parse(reader["PAYMENT"].ToString()) : 0M, reader["SETTLE_ACCOUNT"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["DEPOSIT"].ToString() != "") ? decimal.Parse(reader["DEPOSIT"].ToString()) : 0M, reader["WH_LOAD"].ToString(), reader["MANAGE_TYPE"].ToString(), (reader["OVERDUE_RENT"].ToString() != "") ? decimal.Parse(reader["OVERDUE_RENT"].ToString()) : 0M, (reader["LOWEST_INBOUND_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INBOUND_CHARGE"].ToString()) : 0M, reader["LOWEST_INBOUND_TAX_GROUP"].ToString(), reader["LOWEST_INBOUND_GENERAL_LEDGER"].ToString(), (reader["LOWEST_OUTBOUND_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_OUTBOUND_CHARGE"].ToString()) : 0M, reader["LOWEST_OUTBOUND_TAX_GROUP"].ToString(), reader["LOWEST_OUTBOUND_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INITIAL_STORAGE_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INITIAL_STORAGE_CHARGE"].ToString()) : 0M, reader["LOWEST_INITIAL_STORAGE_TAX_GROUP"].ToString(), reader["LOWEST_INITIAL_STORAGE_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INBOUND_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INBOUND_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_INBOUND_BILL_TAX_GROUP"].ToString(), reader["LOWEST_INBOUND_BILL_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INITIAL_STORAGE_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INITIAL_STORAGE_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP"].ToString(), reader["LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER"].ToString(), (reader["LOWEST_RECURRING_STORAGE_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_RECURRING_STORAGE_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP"].ToString(), reader["LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER"].ToString(), (reader["ALL_BILLS_LOWEST_CHARGE"].ToString() != "") ? decimal.Parse(reader["ALL_BILLS_LOWEST_CHARGE"].ToString()) : 0M, reader["ALL_BILLS_LOWEST_TAX_GROUP"].ToString(), reader["ALL_BILLS_LOWEST_GENERAL_LEDGER"].ToString(), reader["USER1"].ToString(), reader["USER2"].ToString(), reader["USER3"].ToString(), reader["USER4"].ToString(), reader["USER5"].ToString(), reader["USER6"].ToString(), reader["USER7"].ToString(), reader["USER8"].ToString(), reader["USER9"].ToString(), reader["USER10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["DEPARTMENT_ID"].ToString(), reader["IS_AUDIT"].ToString(), reader["REMARK"].ToString());
                }
            }
            return contractInfo;
        }

        public ContractInfo GetContractByID(DataBase dataBase, DbTransaction tran, string contractID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_CONTRACT.Replace("@_@", whLoginID) + " WHERE CONTRACT_ID = @CONTRACT_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTRACT_ID", contractID) };
            ContractInfo contractInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                contractInfo = new ContractInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CONTRACT_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["DESCR"].ToString(), reader["CONTRACT_TYPE"].ToString(), (reader["PAYMENT"].ToString() != "") ? decimal.Parse(reader["PAYMENT"].ToString()) : 0M, reader["SETTLE_ACCOUNT"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["DEPOSIT"].ToString() != "") ? decimal.Parse(reader["DEPOSIT"].ToString()) : 0M, reader["WH_LOAD"].ToString(), reader["MANAGE_TYPE"].ToString(), (reader["OVERDUE_RENT"].ToString() != "") ? decimal.Parse(reader["OVERDUE_RENT"].ToString()) : 0M, (reader["LOWEST_INBOUND_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INBOUND_CHARGE"].ToString()) : 0M, reader["LOWEST_INBOUND_TAX_GROUP"].ToString(), reader["LOWEST_INBOUND_GENERAL_LEDGER"].ToString(), (reader["LOWEST_OUTBOUND_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_OUTBOUND_CHARGE"].ToString()) : 0M, reader["LOWEST_OUTBOUND_TAX_GROUP"].ToString(), reader["LOWEST_OUTBOUND_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INITIAL_STORAGE_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INITIAL_STORAGE_CHARGE"].ToString()) : 0M, reader["LOWEST_INITIAL_STORAGE_TAX_GROUP"].ToString(), reader["LOWEST_INITIAL_STORAGE_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INBOUND_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INBOUND_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_INBOUND_BILL_TAX_GROUP"].ToString(), reader["LOWEST_INBOUND_BILL_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INITIAL_STORAGE_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INITIAL_STORAGE_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP"].ToString(), reader["LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER"].ToString(), (reader["LOWEST_RECURRING_STORAGE_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_RECURRING_STORAGE_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP"].ToString(), reader["LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER"].ToString(), (reader["ALL_BILLS_LOWEST_CHARGE"].ToString() != "") ? decimal.Parse(reader["ALL_BILLS_LOWEST_CHARGE"].ToString()) : 0M, reader["ALL_BILLS_LOWEST_TAX_GROUP"].ToString(), reader["ALL_BILLS_LOWEST_GENERAL_LEDGER"].ToString(), reader["USER1"].ToString(), reader["USER2"].ToString(), reader["USER3"].ToString(), reader["USER4"].ToString(), reader["USER5"].ToString(), reader["USER6"].ToString(), reader["USER7"].ToString(), reader["USER8"].ToString(), reader["USER9"].ToString(), reader["USER10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["DEPARTMENT_ID"].ToString(), reader["IS_AUDIT"].ToString(), reader["REMARK"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return contractInfo;
        }

        public ContractInfo GetContractByOwnerID(string ownerID, string isAudit, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_CONTRACT.Replace("@_@", whLoginID) + " WHERE IS_AUDIT = @IS_AUDIT AND OWNER_ID = @OWNER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@IS_AUDIT", isAudit), new SqlParameter("@OWNER_ID", ownerID) };
            ContractInfo contractInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    contractInfo = new ContractInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CONTRACT_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["DESCR"].ToString(), reader["CONTRACT_TYPE"].ToString(), (reader["PAYMENT"].ToString() != "") ? decimal.Parse(reader["PAYMENT"].ToString()) : 0M, reader["SETTLE_ACCOUNT"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["DEPOSIT"].ToString() != "") ? decimal.Parse(reader["DEPOSIT"].ToString()) : 0M, reader["WH_LOAD"].ToString(), reader["MANAGE_TYPE"].ToString(), (reader["OVERDUE_RENT"].ToString() != "") ? decimal.Parse(reader["OVERDUE_RENT"].ToString()) : 0M, (reader["LOWEST_INBOUND_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INBOUND_CHARGE"].ToString()) : 0M, reader["LOWEST_INBOUND_TAX_GROUP"].ToString(), reader["LOWEST_INBOUND_GENERAL_LEDGER"].ToString(), (reader["LOWEST_OUTBOUND_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_OUTBOUND_CHARGE"].ToString()) : 0M, reader["LOWEST_OUTBOUND_TAX_GROUP"].ToString(), reader["LOWEST_OUTBOUND_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INITIAL_STORAGE_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INITIAL_STORAGE_CHARGE"].ToString()) : 0M, reader["LOWEST_INITIAL_STORAGE_TAX_GROUP"].ToString(), reader["LOWEST_INITIAL_STORAGE_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INBOUND_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INBOUND_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_INBOUND_BILL_TAX_GROUP"].ToString(), reader["LOWEST_INBOUND_BILL_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INITIAL_STORAGE_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INITIAL_STORAGE_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP"].ToString(), reader["LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER"].ToString(), (reader["LOWEST_RECURRING_STORAGE_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_RECURRING_STORAGE_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP"].ToString(), reader["LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER"].ToString(), (reader["ALL_BILLS_LOWEST_CHARGE"].ToString() != "") ? decimal.Parse(reader["ALL_BILLS_LOWEST_CHARGE"].ToString()) : 0M, reader["ALL_BILLS_LOWEST_TAX_GROUP"].ToString(), reader["ALL_BILLS_LOWEST_GENERAL_LEDGER"].ToString(), reader["USER1"].ToString(), reader["USER2"].ToString(), reader["USER3"].ToString(), reader["USER4"].ToString(), reader["USER5"].ToString(), reader["USER6"].ToString(), reader["USER7"].ToString(), reader["USER8"].ToString(), reader["USER9"].ToString(), reader["USER10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["DEPARTMENT_ID"].ToString(), reader["IS_AUDIT"].ToString(), reader["REMARK"].ToString());
                }
            }
            return contractInfo;
        }

        public DataSet GetContractByQueryList(List<string> sqlWhere, ContractQueryEntity contractQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CONTRACT_LANGUAGE.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!contractQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, contractQuery.CurrentPage, contractQuery.PageSize, contractQuery.SortField, contractQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetContractByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, ContractQueryEntity contractQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CONTRACT_LANGUAGE.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!contractQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, contractQuery.CurrentPage, contractQuery.PageSize, contractQuery.SortField, contractQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<ContractInfo> GetContractByWhere(string where, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_CONTRACT.Replace("@_@", whLoginID) + where;
            List<ContractInfo> contractInfos = new List<ContractInfo>();
            ContractInfo contractInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    DateTime CS00001;
                    contractInfo = new ContractInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CONTRACT_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["DESCR"].ToString(), reader["CONTRACT_TYPE"].ToString(), (reader["PAYMENT"].ToString() != "") ? decimal.Parse(reader["PAYMENT"].ToString()) : 0M, reader["SETTLE_ACCOUNT"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00001 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00001 = new DateTime()), (reader["DEPOSIT"].ToString() != "") ? decimal.Parse(reader["DEPOSIT"].ToString()) : 0M, reader["WH_LOAD"].ToString(), reader["MANAGE_TYPE"].ToString(), (reader["OVERDUE_RENT"].ToString() != "") ? decimal.Parse(reader["OVERDUE_RENT"].ToString()) : 0M, (reader["LOWEST_INBOUND_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INBOUND_CHARGE"].ToString()) : 0M, reader["LOWEST_INBOUND_TAX_GROUP"].ToString(), reader["LOWEST_INBOUND_GENERAL_LEDGER"].ToString(), (reader["LOWEST_OUTBOUND_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_OUTBOUND_CHARGE"].ToString()) : 0M, reader["LOWEST_OUTBOUND_TAX_GROUP"].ToString(), reader["LOWEST_OUTBOUND_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INITIAL_STORAGE_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INITIAL_STORAGE_CHARGE"].ToString()) : 0M, reader["LOWEST_INITIAL_STORAGE_TAX_GROUP"].ToString(), reader["LOWEST_INITIAL_STORAGE_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INBOUND_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INBOUND_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_INBOUND_BILL_TAX_GROUP"].ToString(), reader["LOWEST_INBOUND_BILL_GENERAL_LEDGER"].ToString(), (reader["LOWEST_INITIAL_STORAGE_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_INITIAL_STORAGE_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP"].ToString(), reader["LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER"].ToString(), (reader["LOWEST_RECURRING_STORAGE_BILL_CHARGE"].ToString() != "") ? decimal.Parse(reader["LOWEST_RECURRING_STORAGE_BILL_CHARGE"].ToString()) : 0M, reader["LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP"].ToString(), reader["LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER"].ToString(), (reader["ALL_BILLS_LOWEST_CHARGE"].ToString() != "") ? decimal.Parse(reader["ALL_BILLS_LOWEST_CHARGE"].ToString()) : 0M, reader["ALL_BILLS_LOWEST_TAX_GROUP"].ToString(), reader["ALL_BILLS_LOWEST_GENERAL_LEDGER"].ToString(), reader["USER1"].ToString(), reader["USER2"].ToString(), reader["USER3"].ToString(), reader["USER4"].ToString(), reader["USER5"].ToString(), reader["USER6"].ToString(), reader["USER7"].ToString(), reader["USER8"].ToString(), reader["USER9"].ToString(), reader["USER10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00001 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00001 = new DateTime()), reader["DEPARTMENT_ID"].ToString(), reader["IS_AUDIT"].ToString(), reader["REMARK"].ToString());
                    contractInfos.Add(contractInfo);
                }
            }
            return contractInfos;
        }

        public int InsertContract(ContractInfo contractInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                contractInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Contract_Parameters(contractInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CONTRACT.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertContract(DataBase dataBase, DbTransaction tran, ContractInfo contractInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                contractInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Contract_Parameters(contractInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CONTRACT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Contract_Parameters(ContractInfo contractInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CONTRACT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DESCR, SqlDbType.VarChar, 100), new SqlParameter(this.CONTRACT_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.PAYMENT, SqlDbType.Decimal, 13), new SqlParameter(this.SETTLE_ACCOUNT, SqlDbType.VarChar, 20), new SqlParameter(this.START_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.END_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.DEPOSIT, SqlDbType.Decimal, 13), new SqlParameter(this.WH_LOAD, SqlDbType.VarChar, 20), new SqlParameter(this.MANAGE_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.OVERDUE_RENT, SqlDbType.Decimal, 13), new SqlParameter(this.LOWEST_INBOUND_CHARGE, SqlDbType.Decimal, 13), new SqlParameter(this.LOWEST_INBOUND_TAX_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_INBOUND_GENERAL_LEDGER, SqlDbType.VarChar, 20), 
                new SqlParameter(this.LOWEST_OUTBOUND_CHARGE, SqlDbType.Decimal, 13), new SqlParameter(this.LOWEST_OUTBOUND_TAX_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_OUTBOUND_GENERAL_LEDGER, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_INITIAL_STORAGE_CHARGE, SqlDbType.Decimal, 13), new SqlParameter(this.LOWEST_INITIAL_STORAGE_TAX_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_INITIAL_STORAGE_GENERAL_LEDGER, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_INBOUND_BILL_CHARGE, SqlDbType.Decimal, 13), new SqlParameter(this.LOWEST_INBOUND_BILL_TAX_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_INBOUND_BILL_GENERAL_LEDGER, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_INITIAL_STORAGE_BILL_CHARGE, SqlDbType.Decimal, 13), new SqlParameter(this.LOWEST_INITIAL_STORAGE_BILL_TAX_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_INITIAL_STORAGE_BILL_GENERAL_LEDGER, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_RECURRING_STORAGE_BILL_CHARGE, SqlDbType.Decimal, 13), new SqlParameter(this.LOWEST_RECURRING_STORAGE_BILL_TAX_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_RECURRING_STORAGE_BILL_GENERAL_LEDGER, SqlDbType.VarChar, 20), new SqlParameter(this.ALL_BILLS_LOWEST_CHARGE, SqlDbType.Decimal, 13), 
                new SqlParameter(this.ALL_BILLS_LOWEST_TAX_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.ALL_BILLS_LOWEST_GENERAL_LEDGER, SqlDbType.VarChar, 20), new SqlParameter(this.USER1, SqlDbType.VarChar, 30), new SqlParameter(this.USER2, SqlDbType.VarChar, 30), new SqlParameter(this.USER3, SqlDbType.VarChar, 30), new SqlParameter(this.USER4, SqlDbType.VarChar, 30), new SqlParameter(this.USER5, SqlDbType.VarChar, 30), new SqlParameter(this.USER6, SqlDbType.VarChar, 30), new SqlParameter(this.USER7, SqlDbType.VarChar, 30), new SqlParameter(this.USER8, SqlDbType.VarChar, 30), new SqlParameter(this.USER9, SqlDbType.VarChar, 30), new SqlParameter(this.USER10, SqlDbType.VarChar, 30), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.DEPARTMENT_ID, SqlDbType.VarChar, 30), 
                new SqlParameter(this.IS_AUDIT, SqlDbType.VarChar, 1), new SqlParameter(this.REMARK, SqlDbType.VarChar, 300)
             };
            if (!string.IsNullOrEmpty(contractInfo.WhID))
            {
                paramArray[0].Value = contractInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = contractInfo.ContractID;
            paramArray[2].Value = contractInfo.OwnerID;
            if (!string.IsNullOrEmpty(contractInfo.Descr))
            {
                paramArray[3].Value = contractInfo.Descr;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            paramArray[4].Value = contractInfo.ContractType;
            paramArray[5].Value = contractInfo.Payment;
            paramArray[6].Value = contractInfo.SettleAccount;
            paramArray[7].Value = contractInfo.StartTime;
            paramArray[8].Value = contractInfo.EndTime;
            paramArray[9].Value = contractInfo.Deposit;
            if (!string.IsNullOrEmpty(contractInfo.WhLoad))
            {
                paramArray[10].Value = contractInfo.WhLoad;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.ManageType))
            {
                paramArray[11].Value = contractInfo.ManageType;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            paramArray[12].Value = contractInfo.OverdueRent;
            paramArray[13].Value = contractInfo.LowestInboundCharge;
            if (!string.IsNullOrEmpty(contractInfo.LowestInboundTaxGroup))
            {
                paramArray[14].Value = contractInfo.LowestInboundTaxGroup;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.LowestInboundGeneralLedger))
            {
                paramArray[15].Value = contractInfo.LowestInboundGeneralLedger;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            paramArray[0x10].Value = contractInfo.LowestOutboundCharge;
            if (!string.IsNullOrEmpty(contractInfo.LowestOutboundTaxGroup))
            {
                paramArray[0x11].Value = contractInfo.LowestOutboundTaxGroup;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.LowestOutboundGeneralLedger))
            {
                paramArray[0x12].Value = contractInfo.LowestOutboundGeneralLedger;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            paramArray[0x13].Value = contractInfo.LowestInitialStorageCharge;
            if (!string.IsNullOrEmpty(contractInfo.LowestInitialStorageTaxGroup))
            {
                paramArray[20].Value = contractInfo.LowestInitialStorageTaxGroup;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.LowestInitialStorageGeneralLedger))
            {
                paramArray[0x15].Value = contractInfo.LowestInitialStorageGeneralLedger;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            paramArray[0x16].Value = contractInfo.LowestInboundBillCharge;
            if (!string.IsNullOrEmpty(contractInfo.LowestInboundBillTaxGroup))
            {
                paramArray[0x17].Value = contractInfo.LowestInboundBillTaxGroup;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.LowestInboundBillGeneralLedger))
            {
                paramArray[0x18].Value = contractInfo.LowestInboundBillGeneralLedger;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            paramArray[0x19].Value = contractInfo.LowestInitialStorageBillCharge;
            if (!string.IsNullOrEmpty(contractInfo.LowestInitialStorageBillTaxGroup))
            {
                paramArray[0x1a].Value = contractInfo.LowestInitialStorageBillTaxGroup;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.LowestInitialStorageBillGeneralLedger))
            {
                paramArray[0x1b].Value = contractInfo.LowestInitialStorageBillGeneralLedger;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            paramArray[0x1c].Value = contractInfo.LowestRecurringStorageBillCharge;
            if (!string.IsNullOrEmpty(contractInfo.LowestRecurringStorageBillTaxGroup))
            {
                paramArray[0x1d].Value = contractInfo.LowestRecurringStorageBillTaxGroup;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.LowestRecurringStorageBillGeneralLedger))
            {
                paramArray[30].Value = contractInfo.LowestRecurringStorageBillGeneralLedger;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            paramArray[0x1f].Value = contractInfo.AllBillsLowestCharge;
            if (!string.IsNullOrEmpty(contractInfo.AllBillsLowestTaxGroup))
            {
                paramArray[0x20].Value = contractInfo.AllBillsLowestTaxGroup;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.AllBillsLowestGeneralLedger))
            {
                paramArray[0x21].Value = contractInfo.AllBillsLowestGeneralLedger;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User1))
            {
                paramArray[0x22].Value = contractInfo.User1;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User2))
            {
                paramArray[0x23].Value = contractInfo.User2;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User3))
            {
                paramArray[0x24].Value = contractInfo.User3;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User4))
            {
                paramArray[0x25].Value = contractInfo.User4;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User5))
            {
                paramArray[0x26].Value = contractInfo.User5;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User6))
            {
                paramArray[0x27].Value = contractInfo.User6;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User7))
            {
                paramArray[40].Value = contractInfo.User7;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User8))
            {
                paramArray[0x29].Value = contractInfo.User8;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User9))
            {
                paramArray[0x2a].Value = contractInfo.User9;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(contractInfo.User10))
            {
                paramArray[0x2b].Value = contractInfo.User10;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            paramArray[0x2c].Value = contractInfo.IsActive;
            paramArray[0x2d].Value = contractInfo.CreatedBy;
            paramArray[0x2e].Value = contractInfo.UpdatedBy;
            paramArray[0x2f].Value = contractInfo.DepartmentID;
            paramArray[0x30].Value = contractInfo.IsAudit;
            paramArray[0x31].Value = contractInfo.Remark;
            return paramArray;
        }

        public int UpdateContract(ContractInfo contractInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                contractInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Contract_Parameters(contractInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CONTRACT.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateContract(DataBase dataBase, DbTransaction tran, ContractInfo contractInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                contractInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Contract_Parameters(contractInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CONTRACT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

